﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Text;

/// <summary>
/// 
/// </summary>
public partial class UnusedTranslations : System.Web.UI.Page
{
    private Database db;
    /// <summary>
    /// Handles the Load event of the Page control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT DISTINCT language FROM translations";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataTable dt = new DataTable();
                dt.Columns.Add("language");
                dt.Columns.Add("englishname");
                DbDataReader reader = cmd.ExecuteReader();
                string abbrev;
                while(reader.Read()) {
                    abbrev = reader.GetString(0);
                    if(abbrev.Length > 0) {
                        dt.Rows.Add(abbrev, System.Globalization.CultureInfo.GetCultureInfo(abbrev).EnglishName);
                    }
                }
                reader.Close();
                dt.DefaultView.Sort = "englishname";
                radLanguage.DataSource = dt;
                radLanguage.DataValueField = "language";
                radLanguage.DataTextField = "englishname";
                radLanguage.DataBind();
                if(radLanguage.Items.Count > 0) {
                    radLanguage.SelectedIndex = 0;
                }
                SelectLanguage(null, EventArgs.Empty);
            }
        }
        Page.ClientScript.RegisterClientScriptInclude("main", ResolveUrl("~/js/main.js"));
        Page.ClientScript.RegisterClientScriptInclude("jquery", "http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js");
        Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/json.js"));
        Page.ClientScript.RegisterClientScriptInclude("translationjs", ResolveUrl("~/js/translations.js"));
    }

    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }

    /// <summary>
    /// Selects the language.
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void SelectLanguage(object sender, EventArgs e) {
        if(radLanguage.SelectedItem != null) {
            InitializeDataSource();
            grdFindUnused.DataBind();
        }
    }

    /// <summary>
    /// Initializes the data source.
    /// </summary>
    private void InitializeDataSource() {
        if(radLanguage.SelectedValue.ToLower() == "all") {
                dsTranslations.SelectCommand = @"SELECT t.translationid, t.translation, (SELECT COUNT(translations.language) FROM translations WHERE translations.translationid = t.translationid) AS number 
                                                 FROM translations AS t LEFT JOIN elements AS e ON t.translationid = e.translationid 
                                                 GROUP BY t.translationid, t.translation
                                                 HAVING COUNT(e.translationid) = 0";
            } else {
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        dsTranslations.SelectCommand = @"SELECT t.translationid, t.translation, (SELECT COUNT(translations.language) FROM translations WHERE translations.translationid = t.translationid) AS number 
                                                FROM translations AS t LEFT JOIN elements AS e ON t.translationid = e.translationid 
                                                WHERE language = ?lang
                                                GROUP BY t.translationid, t.translation
                                                HAVING COUNT(e.translationid) = 0";
                        break;
                    case Database.DatabaseType.SQLServer: // TODO: the 'all' option gives no results, need to fix
                        dsTranslations.SelectCommand = @"SELECT t.translationid, t.translation, (SELECT COUNT(translations.language) FROM translations WHERE translations.translationid = t.translationid) AS number 
                                                FROM translations AS t LEFT JOIN elements AS e ON t.translationid = e.translationid 
                                                WHERE language = @lang
                                                GROUP BY t.translationid, t.translation
                                                HAVING COUNT(e.translationid) = 0";
                        break;
                }
        }
        if(dsTranslations.SelectParameters.Count == 0) {
            dsTranslations.SelectParameters.Add("lang", TypeCode.String, radLanguage.SelectedValue);
        } else {
            dsTranslations.SelectParameters["lang"].DefaultValue = radLanguage.SelectedValue;
        }
    }

    /// <summary>
    /// Shows the number of items on all pages of the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.SqlDataSourceStatusEventArgs"/> instance containing the event data.</param>
    protected void dsTranslations_Selected(object sender, SqlDataSourceStatusEventArgs e) {
        lblNumUnused.Text = string.Format("{0} Unused Translations for {1}", e.AffectedRows, radLanguage.SelectedItem.Text);
    }

    /// <summary>
    /// Handles button clicks from the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grdFindUnused_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDataSource();
        grdFindUnused.DataBind();
        switch(e.CommandName) {
            case "deletetranslation":
                PromptDelete(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Handles the PageIndexChanging event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grdFindUnused_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdFindUnused.PageIndex = e.NewPageIndex;
        InitializeDataSource();
        grdFindUnused.DataBind();
    }

    /// <summary>
    /// Handles the Sorting event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grdFindUnused_Sorting(object sender, GridViewSortEventArgs e) {
        string oldExpression = grdFindUnused.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
            if(oldExpression.Length > 0) {
                e.SortExpression = newExpression + "," + oldExpression;
            } else {
                e.SortExpression = newExpression;
            }
        } else {
            e.SortExpression = oldExpression;
        }
        InitializeDataSource();
        grdFindUnused.DataBind();
    }


    #region Deleting
    /// <summary>
    /// Redirects the user to the 'Manage applications' page.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnDeleteSelected_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 1;
        List<string> ids = new List<string>();
        foreach(GridViewRow row in grdFindUnused.Rows) {
            if((row.Cells[2].FindControl("chkSelect") as CheckBox).Checked) {
                ids.Add((row.Cells[2].FindControl("btnDelete") as ImageButton).CommandArgument);
            }
        }
        if(ids.Count == 0) {
            litConfirmDelete.Text = "<p>No translations were selected</p>";
            btnOk.Visible = false;
        } else {
            string[] idAry = ids.ToArray();
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                string inClause = db.GenerateInClause("translationid", idAry, false);
                Session["ids"] = inClause;
                cmd.CommandText = "SELECT COUNT(language) FROM translations WHERE " + inClause;
                try {
                    string number = cmd.ExecuteScalar().ToString();
                    litConfirmDelete.Text = string.Format("<p>Are you sure you want to {0} translations.</p><p>Across all languages, {1} translations will be deleted.</p>", idAry.Length, number);
                } catch {
                    litConfirmDelete.Text = "<p>Error loading translation</p>";
                }
            }
            btnOk.Text = "Delete Translations";
            btnOk.Visible = true;
        }
    }

    /// <summary>
    /// Confirms deletion of a application and removes it from the database.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnOk_Click(object sender, EventArgs e) {
        db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            if(ViewState["transid"] != null) {
                cmd.CommandText = "DELETE FROM translations WHERE translationid = @p";
                cmd.AddParameterWithValue("@p", ViewState["transid"].ToString());
                cmd.ExecuteNonQuery();
                ViewState["transid"] = null;
            } else if(Session["ids"] != null) {
                cmd.CommandText = "DELETE FROM translations WHERE "+Session["ids"].ToString();
                cmd.ExecuteNonQuery();
            }
        }
        mltMain.ActiveViewIndex = 0;
        InitializeDataSource();
        grdFindUnused.DataBind();
    }

    /// <summary>
    /// Prompts to delete a translation.
    /// </summary>
    /// <param name="translationId">The translation ID.</param>
    private void PromptDelete(string translationId) {
        mltMain.ActiveViewIndex = 1;
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT COUNT(language) FROM translations WHERE translationid = @tID";
            cmd.AddParameterWithValue("@tID", translationId);
            ViewState["transid"] = translationId;
            try {
                string number = cmd.ExecuteScalar().ToString();
                if(radLanguage.SelectedValue.ToLower() == "all") {
                    litConfirmDelete.Text = string.Format("<p>Are you sure you want to delete translation #{0}.</p><p>This translation is available in {1} languages, which will also be deleted.</p>", translationId, number);
                } else {
                    cmd.CommandText = "SELECT translation FROM translations WHERE translationid = @tID AND language = @lang";
                    cmd.AddParameterWithValue("@lang", radLanguage.SelectedValue);
                    string translation = cmd.ExecuteScalar().ToString();
                    litConfirmDelete.Text = string.Format("<p>Are you sure you want to delete translation #{0} - '{1}'.</p><p>This translation is available in {2} languages, which will also be deleted.</p>", translationId, translation, number);
                }
            } catch {
                litConfirmDelete.Text = "<p>Error loading translation</p>";
            }
            btnOk.Text = "Delete Translation";
            btnOk.Visible = true;
        }
    }

    /// <summary>
    /// Redirects the user to the 'Unused translations' page.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnCancelDelete_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 0;
        InitializeDataSource();
        grdFindUnused.DataBind();
    }
    #endregion
}
